﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Configuration;
using ARR.Entities;
using ARR.DataModel.Repositories;
using System.Data;
using ARR.Entities.Gateways;
using ARR.Shared;
using System.Data.SqlClient;
using System.Transactions;

namespace ARR.Services.Domain.Importers
{
    //Importadores del Sistema Envoy
    public class EnvoyImporter
    {
        private Country country = null;
        private RangeReview rangeReview = null;

        public EnvoyImporter(RangeReview rangeReview)
        {
            this.rangeReview = rangeReview;
        }

        public Country Country
        {
            get
            {
                if (country == null)
                {
                    if (rangeReview != null)
                    {
                        if (rangeReview.Country != null)
                        {
                            country = rangeReview.Country;
                        }
                        else
                        {
                            var countryRow = CountriesRepository.Select(c => c.Id == rangeReview.CountryId).SingleOrDefault();
                            country = new Country(countryRow);
                        }
                    }
                }

                return country;
            }
        }


        //Importador Unificado
        public IList<ImporterResult> UnifiedImportStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;

            int errorCount = 0;
            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);


                if (Directory.Exists(sourceDirectoryPath))
                {
                    string[] files = Directory.GetFiles(sourceDirectoryPath);

                    if (files.Length == 0)
                    {
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.PreRequisites,
                            Description = Errors.NoFilesToImport,
                            ErrorType = ErrorType.Error,
                            Id = errorCount
                        });
                    }
                    else
                    {
                        importerResults.Add(new ImporterResult
                        {
                            ErrorType = ErrorType.Information,
                            Category = ResultCategory.PreRequisites,
                            Description = Messages.ChequeoArchivosCorrecto
                        });
                    }

                    //SALES
                    foreach (string file in files)
                    {
                        fileName = file;
                        string[] fields;
                        rowNumber = 1;
                        using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                        {
                            line = sr.ReadLine();

                            if (!line.Replace(" ", string.Empty).Equals("Store|Date|UPC|Description|UOM|SalesQuantity|SKUQuantity|UnitAvgCost|UnitPrice|SalesAmount|CostAmount|Margin|GP%|Inv.Level1|Inv.Level2|Inv.Level3"))
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterInvalidHeaderFormat,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
                                });
                            }

                            line = sr.ReadLine();
                            rowNumber++;
                            fields = line.Split('|');
                            if (fields.Count() != 16)
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterErrorInvalidNumberOfFields,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCantidadDeCampos, fileName)
                                });
                            }
                        }
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
                }

            }
            catch (Exception ex)
            {
                errorCount++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> UnifiedImportStep2()
        {
            //LG: Meter culture como parámetro del Country 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempArticleSales].
                TempArticleSalesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();
                List<TempArticleSaleRow> Sales = new List<TempArticleSaleRow>();

                //Categories Cache
                List<CategoryRow> categories = CategoriesRepository.Select();
                List<CategoryMatchRow> categoryMatches = CategoryMatchRepository.Select(x => x.CountryId == rangeReview.CountryId);

                //Stores y Articles
                List<ArticleRow> Articles = ArticlesRepository.Select(art => art.RangeReviewId == rangeReview.Id);
                List<StoreRow> Stores = StoresRepository.Select(st => st.CountryId == Country.Id);

                #region Importo desde los TXT
                //Sales
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    string[] fields;

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        //variables
                        DateTime? date = null;
                        decimal? units = null;
                        decimal? sales = null;
                        decimal? grossMargin = null;
                        decimal? grossSales = null;
                        CategoryRow category;

                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store|Date") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = line.Split('|');

                                if (fields != null && fields.Count() == 16)
                                {
                                    string storeCode = fields[0];
                                    string articleEAN = fields[2];

                                    try
                                    {
                                        //Ventas
                                        date = DateTime.Parse(fields[1]);
                                        units = decimal.Parse(fields[6]); //SKU Quantity
                                        sales = decimal.Parse(fields[9]); //Sales Amount
                                        grossMargin = decimal.Parse(fields[11]); //Margin
                                        grossSales = decimal.Parse(fields[9]); //Sales Amount

                                        //Articulos
                                        int? catId = null;
                                        string cat = fields[14].PadRight(6, '0');
                                        if (cat == "000000")
                                        {
                                            //DB: Si viene sin categoria va a la temporal con 0.
                                            catId = 0;
                                        }
                                        else
                                        {
                                            category = categories.Where(categ => categ.Code == cat).SingleOrDefault();
                                            if (category != null)
                                            {
                                                catId = category.Id;
                                            }
                                            else
                                            {
                                                //Si no la encuentro en el maestro me fijo si ya existe algun categoryMatch para dicha categoria
                                                var match = categoryMatches.SingleOrDefault(x => !string.IsNullOrWhiteSpace(x.Name) && x.Name.ToLower() == fields[14].Trim().ToLower());

                                                if (match != null)
                                                {
                                                    catId = match.CategoryId;
                                                }
                                                else
                                                {
                                                    //DB: Si la categoria no existe se guarda en la temporal con un -1 y se guarda tambien en la temporal en nombre de la categoria original
                                                    catId = -1;
                                                }
                                            }
                                        }


                                        var row = new TempArticleSaleRow
                                        {
                                            Date = date.Value,
                                            Units = Convert.ToInt32(units.Value),
                                            Sales = sales.Value,
                                            GM = grossMargin.Value,
                                            GrossSales = grossSales.Value,

                                            //Info Articulos
                                            RangeReviewId = rangeReview.Id,
                                            CategoryId = catId.Value,
                                            NotFoundCategory = catId == -1 ? fields[14] : null,
                                            ArticleCode = fields[2].Trim().PadLeft(13, '0'),
                                            ArticleName = fields[3].Trim(),
                                            EAN = fields[2].Trim().PadLeft(13, '0'),
                                            UOM = fields[4].Trim(),
                                            Brand = null,
                                            Manufacturer = null,
                                            TaxRate = Country.TaxRate ?? 0,

                                            //Info Tiendas
                                            CountryId = Convert.ToInt16(Country.Id),
                                            StoreCode = fields[0],
                                            StoreName = fields[0],
                                            Region = null
                                        };

                                        //Info Ventas
                                        var store = Stores.Where(x => x.Code == row.StoreCode).FirstOrDefault();
                                        var article = Articles.Where(x => x.Code == row.ArticleCode).FirstOrDefault();
                                        if (store != null)
                                        {
                                            row.StoreId = store.Id;
                                        }
                                        if (article != null)
                                        {
                                            row.ArticleId = article.Id;
                                        }
                                        Sales.Add(row);
                                    }
                                    catch (Exception ex)
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = ex.Message,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion

                #region ChequeoVentaDiaria
                //Agrupo por dia por tienda
                var groupedByDay = from s in Sales
                                   group s by new { s.StoreCode, s.Date.Year, s.Date.Month, s.Date.Day } into g
                                   select new
                                   {
                                       Year = g.Key.Year,
                                       Month = g.Key.Month,
                                       Day = g.Key.Day,
                                       StoreCode = g.Key.StoreCode
                                   };

                DateTime startDate = rangeReview.HasPriorYear ? rangeReview.FirstMonth.AddMonths(-12) : rangeReview.FirstMonth;
                int months = rangeReview.HasPriorYear ? rangeReview.TotalMonths + 12 : rangeReview.TotalMonths;
                List<string> stores = (from s in groupedByDay group s by s.StoreCode into g select g.Key).ToList();
                List<DateTime> dates = new List<DateTime>();
                for (int i = 0; i < months; i++)
                {
                    DateTime date = startDate.AddMonths(i);
                    //Loopeo por los dias de cada mes
                    for (int m = 0; m < date.AddMonths(1).AddDays(-1).Day; m++)
                    {
                        dates.Add(date.AddDays(m));
                    }
                }

                var emptyTableQuery = from dt in dates
                                      from s in stores
                                      select new
                                      {
                                          Year = dt.Year,
                                          Month = dt.Month,
                                          Day = dt.Day,
                                          StoreCode = s
                                      };

                var finalQuery = from e in emptyTableQuery
                                 join g in groupedByDay on new { e.Year, e.Month, e.Day, e.StoreCode } equals new { g.Year, g.Month, g.Day, g.StoreCode } into dtg
                                 from j in dtg.DefaultIfEmpty()
                                 select new { Year = e.Year, Month = e.Month, Day = e.Day, StoreCode = e.StoreCode, Missing = j == null ? true : false };

                List<TempArticleSaleRow> articlesAverageMonthlySales = (from s in Sales
                                                                        group s by new
                                                                        {
                                                                            s.CountryId,
                                                                            s.StoreId,
                                                                            s.StoreCode,
                                                                            s.StoreName,
                                                                            s.Date.Year,
                                                                            s.Date.Month,
                                                                            s.ArticleId,
                                                                            s.ArticleCode,
                                                                            s.ArticleName,
                                                                            s.EAN,
                                                                            s.UOM,
                                                                            s.CategoryId,
                                                                            s.NotFoundCategory,
                                                                            s.TaxRate
                                                                        } into g
                                                                        select new TempArticleSaleRow
                                                                        {
                                                                            CountryId = g.Key.CountryId,
                                                                            StoreId = g.Key.StoreId,
                                                                            StoreCode = g.Key.StoreCode,
                                                                            StoreName = g.Key.StoreName,
                                                                            Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                                                                            ArticleId = g.Key.ArticleId,
                                                                            ArticleCode = g.Key.ArticleCode,
                                                                            ArticleName = g.Key.ArticleName,
                                                                            EAN = g.Key.EAN,
                                                                            UOM = g.Key.UOM,
                                                                            CategoryId = g.Key.CategoryId,
                                                                            NotFoundCategory = g.Key.NotFoundCategory,
                                                                            Units = Convert.ToInt32(g.Average(x => x.Units)),
                                                                            Sales = g.Average(x => x.Sales),
                                                                            GrossSales = g.Average(x => x.GrossSales),
                                                                            GM = g.Average(x => x.GM),
                                                                            TaxRate = g.Key.TaxRate
                                                                        }).ToList();

                int days = 0;
                int storesCount = finalQuery.Where(x => x.Missing).Select(x => x.StoreCode).Distinct().Count();
                //Si no hay registros para un dia determinado agrego un error
                foreach (var m in finalQuery.Where(x => x.Missing))
                {
                    days++;
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        //INFO: Lila los prados y venezuela tienen dias faltantes.
                        Category = ResultCategory.TemporalTable,
                        Description = string.Format("No hay ventas el día {0} para la tienda {1}", new DateTime(m.Year, m.Month, m.Day).ToShortDateString(), m.StoreCode),
                        ErrorType = ErrorType.Warning,
                        Param1 = new DateTime(m.Year, m.Month, m.Day).ToString("mm-MMMM"),
                        Param2 = m.StoreCode,
                        Id = errorCount
                    });

                    //TODO: insertar venta promedio para ese dia para cada producto?
                    Sales.AddRange(articlesAverageMonthlySales.Where(x => x.Date.Year == m.Year && x.Date.Month == m.Month && x.StoreCode == m.StoreCode));
                }

                if (days > 0)
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.TemporalTable,
                        Description = string.Format("Se encontraron {0} días de ventas faltantes para {1} en total. Solicite nuevamente la informacion para estas tiendas en los meses informados. Si continua se completaran los días faltantes de ventas con el promedio de venta diario del mes correspondiente.", days, storesCount),
                        ErrorType = ErrorType.Information,
                        Id = errorCount
                    });
                }

                #endregion

                #region Sumarizo las ventas y armo el DataTable
                //Sumarizo
                var q = from s in Sales
                        group s by new
                        {
                            s.CountryId,
                            s.StoreId,
                            s.StoreCode,
                            s.StoreName,
                            s.Region,
                            s.CategoryId,
                            s.NotFoundCategory,
                            s.ArticleId,
                            s.ArticleCode,
                            s.ArticleName,
                            s.EAN,
                            s.UOM,
                            s.Brand,
                            s.Manufacturer,
                            s.TaxRate,
                            s.Date.Year,
                            s.Date.Month
                        } into g
                        select new
                        {
                            //Ventas
                            ArticleId = g.Key.ArticleId,
                            StoreId = g.Key.StoreId,
                            Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                            Units = g.Sum(x => x.Units),
                            Sales = g.Sum(x => x.Sales),
                            GM = g.Sum(x => x.GM),
                            GrossSales = g.Sum(x => x.GrossSales),

                            //Articulos
                            RangeReviewId = rangeReview.Id,
                            CategoryId = g.Key.CategoryId,
                            NotFoundCategory = g.Key.NotFoundCategory,
                            ArticleCode = g.Key.ArticleCode,
                            ArticleName = g.Key.ArticleName,
                            EAN = g.Key.EAN,
                            UOM = g.Key.UOM,
                            Brand = g.Key.Brand,
                            Manufacturer = g.Key.Manufacturer,
                            TaxRate = g.Key.TaxRate,

                            //Stores
                            CountryId = g.Key.CountryId,
                            StoreCode = g.Key.StoreCode,
                            StoreName = g.Key.StoreName,
                            Region = g.Key.Region
                        };

                //Construyo el DataTable en base a los datos sumarizados
                foreach (var sale in q)
                {
                    DataRow datarow = sourceTable.NewRow();

                    //Ventas
                    if (sale.StoreId.HasValue)
                    {
                        datarow["StoreId"] = sale.StoreId.Value;
                    }
                    else
                    {
                        datarow["StoreId"] = DBNull.Value;
                    }
                    if (sale.ArticleId.HasValue)
                    {
                        datarow["ArticleId"] = sale.ArticleId.Value;
                    }
                    else
                    {
                        datarow["ArticleId"] = DBNull.Value;
                    }
                    datarow["StoreCode"] = sale.StoreCode;
                    datarow["ArticleCode"] = sale.ArticleCode;
                    datarow["Date"] = sale.Date;
                    datarow["Units"] = sale.Units;
                    datarow["Sales"] = sale.Sales;
                    datarow["GM"] = sale.GM;
                    datarow["GrossSales"] = sale.GrossSales;
                    if (sale.Units > 0)
                    {
                        datarow["Price"] = sale.GrossSales / sale.Units;
                    }

                    //Articulos
                    datarow["RangeReviewId"] = sale.RangeReviewId;
                    datarow["CategoryId"] = sale.CategoryId;
                    datarow["NotFoundCategory"] = sale.NotFoundCategory;
                    datarow["ArticleCode"] = sale.ArticleCode;
                    datarow["ArticleName"] = sale.ArticleName;
                    datarow["EAN"] = sale.EAN;
                    datarow["UOM"] = sale.UOM;
                    datarow["Brand"] = sale.Brand;
                    datarow["Manufacturer"] = sale.Manufacturer;
                    datarow["TaxRate"] = sale.TaxRate;

                    //Tiendas
                    datarow["CountryId"] = sale.CountryId;
                    datarow["StoreCode"] = sale.StoreCode;
                    datarow["StoreName"] = sale.StoreName;
                    datarow["Region"] = sale.Region;

                    sourceTable.Rows.Add(datarow);
                }
                #endregion

                // new method: SQLBulkCopy:
                TempArticleSalesRepository.BulkCopy(sourceTable);
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            TempArticlesRepository.TruncateTable();
            TempArticlesRepository.InsertDataFromTempSales();

            //TempArticlesRepository.ConcatenateUomAndCodeForRepeatedCodes();
            //TempArticlesRepository.ConcatenateUomAndCodeForRepeatedNames();

            TempStoresRepository.TruncateTable();
            TempStoresRepository.InsertDataFromTempSales();

            return importerResults;
        }


        //Articulos
        public void ImportArticleMaster()
        {
            IList<ImporterResult> resultsStep1 = null;
            IList<ImporterResult> resultsStep2 = null;
            IList<ImporterResult> resultsStep3 = null;
            IList<ImporterResult> resultsStep4 = null;

            try
            {
                resultsStep1 = ImportArticleMasterStep1();
                WriteTextResults("Step1Results", resultsStep1);

                if (!resultsStep1.Any(x => x.ErrorType == ErrorType.Error))
                {
                    resultsStep2 = ImportArticleMasterStep2();
                    WriteTextResults("Step2Results", resultsStep2);

                    resultsStep3 = ImportArticleMasterStep3();
                    WriteTextResults("Step3Results", resultsStep3);

                    resultsStep4 = ImportArticleMasterStep4();
                    WriteTextResults("Step4Results", resultsStep4);
                }
            }
            catch (Exception ex)
            {
                string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
                //LG: chequear la existencia de la carpeta Results en cada metodo
                if (!Directory.Exists(resultsPath))
                {
                    Directory.CreateDirectory(resultsPath);
                }
                string fullName = Path.Combine(resultsPath, "UnhandledErrors.txt");

                using (StreamWriter sw = new StreamWriter(fullName, true))
                {
                    sw.WriteLine("Exception: ", ex.Message);
                    sw.WriteLine(sw.NewLine);
                    sw.WriteLine("Stack Trace: ", ex.StackTrace);
                }
            }
        }

        public IList<ImporterResult> ImportArticleMasterStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;

            int errorCount = 0;
            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);
                if (Directory.Exists(sourceDirectoryPath))
                {
                    string[] files = Directory.GetFiles(sourceDirectoryPath);

                    if (files.Length == 0)
                    {
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.PreRequisites,
                            Description = Errors.NoFilesToImport,
                            ErrorType = ErrorType.Error,
                            Id = errorCount
                        });
                    }
                    else
                    {
                        importerResults.Add(new ImporterResult
                        {
                            ErrorType = ErrorType.Information,
                            Category = ResultCategory.PreRequisites,
                            Description = Messages.ChequeoArchivosCorrecto
                        });
                    }

                    //SALES
                    foreach (string file in files)
                    {
                        fileName = file;
                        string[] fields;
                        rowNumber = 1;
                        using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                        {
                            line = sr.ReadLine();

                            if (!line.Replace(" ", string.Empty).Equals("Store|Date|UPC|Description|UOM|SalesQuantity|SKUQuantity|UnitAvgCost|UnitPrice|SalesAmount|CostAmount|Margin|GP%|Inv.Level1|Inv.Level2|Inv.Level3"))
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterInvalidHeaderFormat,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
                                });
                            }

                            line = sr.ReadLine();
                            rowNumber++;
                            fields = line.Split('|');
                            if (fields.Count() != 16)
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterErrorInvalidNumberOfFields,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCantidadDeCampos, fileName)
                                });
                            }
                        }
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
                }
            }
            catch (Exception ex)
            {
                errorCount++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleMasterStep2()
        {
            //LG: El separador de decimales es . pero la fecha viene en formato dd/mm/yyyy
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            int errorCount = 0;
            string fileName = null;
            int? rowNumber = null;
            string line;

            try
            {
                // Limpio las tablas temporales [TempArticles].
                TempArticlesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                //LG: Ver como es esto en el caso de que no se trabaje con NACS
                //Categories Cache
                List<CategoryRow> categories = CategoriesRepository.Select();
                List<CategoryMatchRow> categoryMatches = CategoryMatchRepository.Select(x => x.CountryId == rangeReview.CountryId);

                CategoryRow category;

                //Articles
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    string[] fields;
                    DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleRow>();

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store|Date") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = line.Split('|');

                                if (fields != null && fields.Count() == 16)
                                {
                                    //LG: las categorias son NACS pero vienen sin los ultimos 2 ceros
                                    int? catId = null;
                                    string cat = fields[14].PadRight(6, '0');
                                    if (cat == "000000")
                                    {
                                        //DB: Si viene sin categoria va a la temporal con 0.
                                        catId = 0;
                                    }
                                    else
                                    {
                                        category = categories.Where(categ => categ.Code == cat).SingleOrDefault();
                                        if (category != null)
                                        {
                                            catId = category.Id;
                                        }
                                        else
                                        {
                                            //Si no la encuentro en el maestro me fijo si ya existe algun categoryMatch para dicha categoria
                                            var match = categoryMatches.SingleOrDefault(x => x.Name.ToLower() == fields[14].Trim().ToLower());

                                            if (match != null)
                                            {
                                                catId = match.CategoryId;
                                            }
                                            else
                                            {
                                                //DB: Si la categoria no existe se guarda en la temporal con un -1 y se guarda tambien en la temporal en nombre de la categoria original
                                                catId = -1;
                                            }
                                        }
                                    }



                                    DataRow datarow = sourceTable.NewRow();
                                    datarow["RangeReviewId"] = rangeReview.Id;
                                    datarow["CategoryId"] = catId;
                                    datarow["NotFoundCategory"] = catId == -1 ? fields[14] : string.Empty;
                                    //LG: Completo el codigo de barras con 0 para llegar a 13 caracteres
                                    datarow["Code"] = fields[2].Trim().PadLeft(13, '0');
                                    datarow["Name"] = fields[3].Trim();
                                    datarow["EAN"] = fields[2].Trim().PadLeft(13, '0');
                                    datarow["UOM"] = fields[4].Trim();
                                    datarow["Brand"] = string.Empty;
                                    datarow["Manufacturer"] = string.Empty;
                                    datarow["TaxRate"] = Country.TaxRate ?? 0;

                                    sourceTable.Rows.Add(datarow);
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                            }
                        }
                    }

                    // new method: SQLBulkCopy:
                    TempArticlesRepository.BulkCopy(sourceTable);

                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.TemporalTable,
                        Description = Messages.ImportacionATablaTemporalCorrecta
                    });
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleMasterStep3()
        {
            //TODO:Checkear si esto dseberia resolverlo el usuario.
            TempArticlesRepository.DeleteDuplicates(); //Elimina registro cor Code-UOM duplicados.

            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int errorCount = 0;

            try
            {
                List<TempArticleRow> TempArticles = TempArticlesRepository.Select();

                //Categories Cache
                List<CategoryRow> categories = CategoriesRepository.Select();

                //ARTICULOS SIN CODIGO. 
                List<TempArticleRow> articlesWithNoCode = TempArticles.Where(art => art.Code == null || art.Code == string.Empty || art.Code == "0000000000000").ToList();

                foreach (TempArticleRow art in articlesWithNoCode)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.TheArticleDoesntHaveCode, art.Name),
                        ErrorType = ErrorType.Error,
                        Id = errorCount,
                        ItemId = art.Id,
                        Workaround = Workaround.EditArticleInGrid,
                        Param2 = art.Name,
                        Param3 = art.Code,
                        Param4 = categories.Any(x => x.Id == art.CategoryId) ? categories.Single(x => x.Id == art.CategoryId).Name : string.Empty
                    });
                }

                if (!articlesWithNoCode.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.NoSeEncontraronArticulosSinCodigo
                    });
                }

                //LG: no estamos distinguiendo entre un producto que no trajo categoria de origen o uno que no matchea la categoria con el maestro
                //DB: Ahora, si viene con 0 vino sin categoria y si viene con -1 no existe la categoria en el maestro.
                //Verifico si hay Articulos sin Categoria.

                //ARTICULOS SIN CATEGORIA
                List<TempArticleRow> articlesWithNoCategory = TempArticles.Where(art => art.CategoryId == 0).ToList();

                //ARTICULOS CON CATEGORIA INEXISTENTE EN EL MAESTRO
                List<TempArticleRow> articlesCategoryNotFound = TempArticles.Where(art => art.CategoryId == -1).ToList();

                //LG: Aca ofrecerle al usuario la posibilidad de recategorizar productos sin categoría
                //LG: Otra posibilidad es que ignore los productos sin categoria
                //DB: Para los productos sin categoria se da la posibilidad de completarlo, y para los que tienen una categoria que no existe en el maestro 
                //se les da la posibilidad de hacer el matcheo con una categoria existente.
                foreach (TempArticleRow art in articlesWithNoCategory)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.TheArticleDoesNotHaveCategory, art.Name),
                        ErrorType = ErrorType.Error,
                        Id = errorCount,
                        ItemId = art.Id,
                        Workaround = Workaround.EditArticleInGrid,
                        Param2 = art.Name,
                        Param3 = art.Code,
                        Param4 = categories.Any(x => x.Id == art.CategoryId) ? categories.Single(x => x.Id == art.CategoryId).Name : string.Empty
                    });

                    //TODO: Estoy eliminando los articulos que vienen sin category, pero deberia ofrecer alguna solución al usuario, alguna posibilidad de que él decida que hacer.          
                    //DB:Eliminar esta linea cuando quede implementada la corrección por el usuario.
                    //int artId = art.Id;
                    //TempArticlesRepository.Delete(a => a.Id == artId);
                }

                foreach (TempArticleRow art in articlesCategoryNotFound)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.CategoryNotFoundInMaster, art.Name),
                        ErrorType = ErrorType.Error,
                        Id = errorCount,
                        ItemId = art.Id,
                        ItemText = art.NotFoundCategory,
                        Workaround = Workaround.MatchCategory,
                        Param2 = art.Name,
                        Param3 = art.Code,
                        Param4 = categories.Any(x => x.Id == art.CategoryId) ? categories.Single(x => x.Id == art.CategoryId).Name : art.NotFoundCategory
                    });

                    //TODO: Estoy eliminando los articulos que vienen sin category, pero deberia ofrecer alguna solución al usuario, alguna posibilidad de que él decida que hacer.          
                    //DB:Eliminar esta linea cuando quede implementada la corrección por el usuario.
                    //int artId = art.Id;
                    //TempArticlesRepository.Delete(a => a.Id == artId);
                }

                if (!articlesWithNoCategory.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.NoSeEncontraronArticulosSinCategoria
                    });
                }

                if (!articlesCategoryNotFound.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.NoSeEncontraronArticulosSinCategoriaEnMaestro
                    });
                }


                //ARTICULOS CON MISMO NAME
                var repeatedNames = from art in TempArticles
                                    group art by art.Name into g
                                    let count = g.Count()
                                    where count > 1
                                    select g.Key;

                var articlesWithSameName = TempArticles.Where(art => repeatedNames.Contains(art.Name));

                foreach (TempArticleRow art in articlesWithSameName)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.RepeatedArticleName, art.Name),
                        ErrorType = ErrorType.Warning,
                        Id = errorCount,
                        ItemId = art.Id,
                        Workaround = Workaround.UnifyRepeatedNames,
                        Param2 = art.Name,
                        Param3 = art.Code,
                        Param4 = categories.Any(x => x.Id == art.CategoryId) ? categories.Single(x => x.Id == art.CategoryId).Name : string.Empty
                    });
                }

                if (!articlesWithSameName.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.ArticulosConMismoNombreYDistintoCodigo
                    });
                }


            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleMasterStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Update del Maestro de Articulos.
                    ArticlesRepository.UpdateFromTempArticle(rangeReview.Id);

                    //Hago Insert del Maestro de Articulos.
                    ArticlesRepository.InsertFromTempArticles(rangeReview.Id);


                    //Actualizo los ArticleIds en la tabla de TempArticleSales si el importador es unificado
                    if (!Country.IsArticleMasterSeparateFile)
                    {
                        ArticlesDomain.UpdateTempArticleSalesArticleIds(rangeReview.Id);
                    }

                    AttributesRepository.UpdateFromTempArticleAttributes(rangeReview.Id);

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempArticlesRepository.TruncateTable();
                AttributesRepository.TruncateTemporalTable();

                //Muevo los archivos a la carpeta Old
                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = Messages.LosArticulosFueronImportadosExitosamente
                });


            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        //Tiendas
        public void ImportStoreMaster()
        {
            IList<ImporterResult> resultsStep1 = null;
            IList<ImporterResult> resultsStep2 = null;
            IList<ImporterResult> resultsStep3 = null;
            IList<ImporterResult> resultsStep4 = null;

            try
            {
                resultsStep1 = ImportStoreMasterStep1();
                WriteTextResults("Step1Results", resultsStep1);

                if (resultsStep1.Count == 0)
                {
                    resultsStep2 = ImportStoreMasterStep2();
                    WriteTextResults("Step2Results", resultsStep2);

                    resultsStep3 = ImportStoreMasterStep3();
                    WriteTextResults("Step3Results", resultsStep3);

                    resultsStep4 = ImportStoreMasterStep4();
                    WriteTextResults("Step4Results", resultsStep4);
                }
            }
            catch (Exception ex)
            {
                string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
                string fullName = Path.Combine(resultsPath, "UnhandledErrors.txt");

                using (StreamWriter sw = new StreamWriter(fullName, true))
                {
                    sw.WriteLine("Exception: ", ex.Message);
                    sw.WriteLine(sw.NewLine);
                    sw.WriteLine("Stack Trace: ", ex.StackTrace);
                }
            }
        }

        public IList<ImporterResult> ImportStoreMasterStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);

                if (Directory.Exists(sourceDirectoryPath))
                {
                    string[] files = Directory.GetFiles(sourceDirectoryPath);

                    if (files.Length == 0)
                    {
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.PreRequisites,
                            Description = Errors.NoFilesToImport,
                            ErrorType = ErrorType.Error,
                            Id = errorCount
                        });
                    }
                    else
                    {
                        importerResults.Add(new ImporterResult
                        {
                            ErrorType = ErrorType.Information,
                            Category = ResultCategory.PreRequisites,
                            Description = Messages.ChequeoArchivosCorrecto
                        });
                    }

                    //SALES
                    foreach (string file in files)
                    {
                        fileName = file;

                        string[] fields;
                        rowNumber = 1;
                        using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                        {
                            line = sr.ReadLine();

                            if (!line.Replace(" ", string.Empty).Contains("Store|Date|UPC|Description|UOM|SalesQuantity|SKUQuantity|UnitAvgCost|UnitPrice|SalesAmount|CostAmount|Margin|GP%|Inv.Level1|Inv.Level2|Inv.Level3"))
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterInvalidHeaderFormat,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
                                });
                            }

                            line = sr.ReadLine();
                            rowNumber++;
                            fields = line.Split('|');
                            if (fields.Count() != 16)
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterErrorInvalidNumberOfFields,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCantidadDeCampos, fileName)
                                });
                            }

                        }
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
                }
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep2()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempStores].
                TempStoresRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                //Stores
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    string[] fields;

                    DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempStoreRow>();

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store|Date") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = line.Split('|');


                                if (fields != null && fields.Count() == 16)
                                {
                                    DataRow datarow = sourceTable.NewRow();
                                    datarow["CountryId"] = Convert.ToInt16(Country.Id);
                                    datarow["Code"] = fields[0];
                                    datarow["Name"] = fields[0];
                                    datarow["Region"] = string.Empty;

                                    sourceTable.Rows.Add(datarow);
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                            }
                        }
                    }

                    // new method: SQLBulkCopy:
                    TempStoresRepository.BulkCopy(sourceTable);

                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.TemporalTable,
                        Description = Messages.ImportacionATablaTemporalCorrecta
                    });
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {

                //Verifico si hay tiendas sin Codigo o Nombre.
                List<TempStoreRow> storesWithoutCode = TempStoresRepository.Select(store => store.Code == null || store.Code == string.Empty);

                foreach (TempStoreRow store in storesWithoutCode)
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.StoreDoesntHaveCode, store.Name),
                        ErrorType = ErrorType.Error,
                        Id = count
                    });

                    //TODO: Estoy eliminando los stores que vienen sin codigo, pero deberia ofrecer alguna solución al usuario, alguna posibilidad de que él decida que hacer.
                    TempStoresRepository.Delete(st => st.Id == store.Id);
                }

                if (!storesWithoutCode.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.NoSeEncontraronTiendasSinCodigo
                    });
                }

            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Update de Maestro de Tiendas
                    StoresRepository.UpdateFromTempStores(Country.Id);

                    //Hago Insert de Maestro Tiendas
                    StoresRepository.InsertFromTempStores(Country.Id, rangeReview.Id);

                    //Actualizo los StoreIds en la tabla de TempArticleSales si el importador es unificado
                    if (!Country.IsStoreMasterSeparateFile)
                    {
                        ArticlesDomain.UpdateTempArticleSalesStoreIds();
                    }

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempStoresRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);


                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = Messages.LasTiendasFueronImportadosExitosamente
                });
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        //Ventas
        public void ImportArticleSales()
        {
            IList<ImporterResult> resultsStep1 = null;
            IList<ImporterResult> resultsStep2 = null;
            IList<ImporterResult> resultsStep3 = null;
            IList<ImporterResult> resultsStep4 = null;

            try
            {
                resultsStep1 = ImportArticleSalesStep1();
                WriteTextResults("Step1Results", resultsStep1);

                if (resultsStep1.Count == 0)
                {
                    resultsStep2 = ImportArticleSalesStep2();
                    WriteTextResults("Step2Results", resultsStep2);

                    resultsStep3 = ImportArticleSalesStep3();
                    WriteTextResults("Step3Results", resultsStep3);

                    resultsStep4 = ImportArticleSalesStep4();
                    WriteTextResults("Step4Results", resultsStep4);
                }
            }
            catch (Exception ex)
            {
                string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
                string fullName = Path.Combine(resultsPath, "UnhandledErrors.txt");

                using (StreamWriter sw = new StreamWriter(fullName, true))
                {
                    sw.WriteLine("Exception: ", ex.Message);
                    sw.WriteLine(sw.NewLine);
                    sw.WriteLine("Stack Trace: ", ex.StackTrace);
                }
            }
        }

        public IList<ImporterResult> ImportArticleSalesStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);

                if (Directory.Exists(sourceDirectoryPath))
                {
                    string[] files = Directory.GetFiles(sourceDirectoryPath);

                    if (files.Length == 0)
                    {
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.PreRequisites,
                            Description = Errors.NoFilesToImport,
                            ErrorType = ErrorType.Error,
                            Id = errorCount
                        });
                    }

                    //SALES
                    foreach (string file in files)
                    {
                        fileName = file;

                        string[] fields;
                        rowNumber = 1;
                        using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                        {
                            line = sr.ReadLine();

                            if (!line.Replace(" ", string.Empty).Equals("Store|Date|UPC|Description|UOM|SalesQuantity|SKUQuantity|UnitAvgCost|UnitPrice|SalesAmount|CostAmount|Margin|GP%|Inv.Level1|Inv.Level2|Inv.Level3"))
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterInvalidHeaderFormat,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }

                            line = sr.ReadLine();
                            rowNumber++;
                            fields = line.Split('|');
                            if (fields.Count() != 16)
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterErrorInvalidNumberOfFields,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }

                        }
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep2()
        {
            //LG: Meter culture como parámetro del Country 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempArticleSales].
                TempArticleSalesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                //Cacheo en memoria los articles y los stores del pais.
                List<ArticleRow> Articles = ArticlesRepository.Select(art => art.RangeReviewId == rangeReview.Id);
                List<StoreRow> Stores = StoresRepository.Select(st => st.CountryId == Country.Id);
                StoreRow store = null;
                ArticleRow article = null;
                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();
                List<ArticleSaleRow> Sales = new List<ArticleSaleRow>();
                //Sales
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    string[] fields;

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        //variables
                        DateTime? date = null;
                        decimal? units = null;
                        decimal? sales = null;
                        decimal? grossMargin = null;
                        decimal? grossSales = null;
                        decimal? price = null;

                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store|Date") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = line.Split('|');

                                if (fields != null && fields.Count() == 16)
                                {
                                    //Chequeo que el store no sea nulo y que exista en el maestro de tiendas.
                                    string storeCode = fields[0];
                                    if (!string.IsNullOrWhiteSpace(storeCode))
                                    {
                                        store = Stores.Where(st => st.Code == storeCode).SingleOrDefault();
                                        if (store == null)
                                        {
                                            errorCount++;
                                            importerResults.Add(new ImporterResult
                                            {
                                                Category = ResultCategory.TemporalTable,
                                                Description = string.Format(Errors.StoreCodeNotFoundInMaster, fields[0], rowNumber),
                                                ErrorType = ErrorType.Error,
                                                Id = errorCount
                                            });
                                        }
                                    }
                                    else
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Description = string.Format(Errors.StoreCodeCantBeNull, rowNumber),
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }

                                    //Chequeo que el Article no sea nulo y que exista en el maestro de Articulos.
                                    string articleCode = fields[2];
                                    string articleUOM = fields[4];
                                    if (!string.IsNullOrWhiteSpace(articleCode))
                                    {
                                        //LG: este chequeo lo hago para cada producto del mismo pais
                                        article = Articles.Where(art => art.Code == articleCode && art.UOM == articleUOM && art.RangeReviewId == rangeReview.Id).SingleOrDefault();
                                        if (article == null)
                                        {
                                            errorCount++;
                                            importerResults.Add(new ImporterResult
                                            {
                                                Category = ResultCategory.TemporalTable,
                                                Description = string.Format(Errors.ArticleEanNotFoundInMaster, articleCode, rowNumber),
                                                ErrorType = ErrorType.Error,
                                                Id = errorCount
                                            });
                                        }
                                    }
                                    else
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Description = string.Format(Errors.ArticleEANCantBeNull, rowNumber),
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }

                                    try
                                    {
                                        //Parseo la fecha
                                        date = DateTime.Parse(fields[1]);

                                        units = decimal.Parse(fields[6]); //SKU Quantity
                                        sales = decimal.Parse(fields[9]); //Sales Amount
                                        grossMargin = decimal.Parse(fields[11]); //Margin
                                        grossSales = decimal.Parse(fields[9]); //Sales Amount
                                        price = units > 0 ? grossSales / units : 0;

                                        //Agrego un registro de venta al DataTable.
                                        if (article != null && store != null)
                                        {
                                            Sales.Add(new ArticleSaleRow
                                            {
                                                StoreId = store.Id,
                                                ArticleId = article.Id,
                                                Date = date.Value,
                                                Units = Convert.ToInt32(units.Value),
                                                Sales = sales.Value,
                                                GM = grossMargin.Value,
                                                GrossSales = grossSales.Value,
                                                Price = price
                                            });
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = ex.Message,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                            }
                        }
                    }
                }

                //Sumarizo
                var q = from s in Sales
                        group s by new { s.StoreId, s.ArticleId, s.Date.Year, s.Date.Month } into g
                        select new
                        {
                            g.Key.StoreId,
                            g.Key.ArticleId,
                            Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                            Units = g.Sum(x => x.Units),
                            Sales = g.Sum(x => x.Sales),
                            GM = g.Sum(x => x.GM),
                            GrossSales = g.Sum(x => x.GrossSales)
                        };

                //Construyo el DataTable en base a los datos sumarizados
                foreach (var sale in q)
                {
                    DataRow datarow = sourceTable.NewRow();
                    datarow["StoreId"] = sale.StoreId;
                    datarow["ArticleId"] = sale.ArticleId;
                    datarow["Date"] = sale.Date;
                    datarow["Units"] = sale.Units;
                    datarow["Sales"] = sale.Sales;
                    datarow["GM"] = sale.GM;
                    datarow["GrossSales"] = sale.GrossSales;
                    if (sale.Units > 0)
                    {
                        datarow["Price"] = sale.GrossSales / sale.Units;
                    }
                    sourceTable.Rows.Add(datarow);
                }

                // new method: SQLBulkCopy:
                TempArticleSalesRepository.BulkCopy(sourceTable);
                
                TempArticleSalesRepository.UpdateArticleIds(rangeReview.Id);
                TempArticleSalesRepository.UpdateStoreIds();
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                //Verifico si hay registros con margenes negativos.
                //List<TempArticleSaleRow> negativeMargins = TempArticleSalesRepository.Select(sale => sale.GM < 0);

                //Cargo los margenes por catgoria
                var categoryMargins = TempArticleSalesCategoryMarginsRepository.Select();
                var theoreticalMargins = TheoreticalMarginsRepository.Select(tm => tm.CountryId == rangeReview.CountryId);

                foreach (var cat in categoryMargins)
                {
                    var tMargin = theoreticalMargins.SingleOrDefault(tm => tm.CategoryId == cat.Id);
                    if (tMargin != null)
                        cat.Margin = tMargin.Margin;

                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.MargenDeLaCategoria, cat.Name, cat.Margin.HasValue ? cat.Margin.Value.ToString("P0") : string.Empty),
                        ErrorType = (cat.Margin.HasValue && (cat.Margin <= 0 || cat.Margin == 1)) ? ErrorType.Warning : ErrorType.Information,
                        Id = count,
                        Workaround = Workaround.CategoryMargin,
                        Param1 = cat.Name,
                        Param2 = cat.Margin.ToString(),
                        ItemId = cat.Id
                    });
                }

                ////Unidades en 0 y ventas o margen <> 0
                //var unitsInZero = TempArticleSalesRepository.Select(sale => sale.Units == 0 && (sale.Sales != 0 || sale.GM != 0));

                //foreach (var sale in unitsInZero)
                //{
                //    importerResults.Add(new ImporterResult
                //    {
                //        Category = ResultCategory.Validation,
                //        Description = string.Format(Messages.UnidadesEnCeroConVentas, sale.ArticleName, sale.StoreName, sale.Date),
                //        ErrorType = ErrorType.Warning,
                //        Id = count,
                //        Workaround = Workaround.UnitsIsZero,
                //        Param1 = sale.Units.ToString(),
                //        Param2 = sale.Sales.ToString(),
                //        Param3 = sale.GM.ToString(),
                //        ItemId = sale.Id
                //    });
                //}


                ////Unidades <> 0 y ventas o margen en 0
                //var salesOrMarginInZero = TempArticleSalesRepository.Select(sale => sale.Units > 0 && (sale.Sales == 0 || sale.GM == 0));

                //foreach (var sale in salesOrMarginInZero)
                //{
                //    importerResults.Add(new ImporterResult
                //    {
                //        Category = ResultCategory.Validation,
                //        Description = string.Format(Messages.VentasEnCeroConUnidades, sale.ArticleName, sale.StoreName, sale.Date),
                //        ErrorType = ErrorType.Warning,
                //        Id = count,
                //        Workaround = Workaround.SalesOrMarginIsZero,
                //        Param1 = sale.Units.ToString(),
                //        Param2 = sale.Sales.ToString(),
                //        Param3 = sale.GM.ToString(),
                //        ItemId = sale.Id
                //    });
                //}

                //Unidades negativas o en cero
                var negativeUnits = TempArticleSalesRepository.Select(sale => sale.Units <= 0);

                foreach (var sale in negativeUnits)
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.UnidadesNegativas, sale.ArticleName, sale.StoreName, sale.Date),
                        ErrorType = ErrorType.Warning,
                        Id = count,
                        Workaround = Workaround.NegativeUnits,
                        Param1 = sale.Units.ToString(),
                        Param2 = sale.Sales.ToString(),
                        Param3 = sale.GM.ToString(),
                        ItemId = sale.Id
                    });
                }

                //Ventas negativas o en cero
                var negativeSales = TempArticleSalesRepository.Select(sale => sale.Sales <= 0);

                foreach (var sale in negativeUnits)
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.VentasNegativas, sale.ArticleName, sale.StoreName, sale.Date),
                        ErrorType = ErrorType.Warning,
                        Id = count,
                        Workaround = Workaround.NegativeUnits,
                        Param1 = sale.Units.ToString(),
                        Param2 = sale.Sales.ToString(),
                        Param3 = sale.GM.ToString(),
                        ItemId = sale.Id
                    });
                }

                //Registros sin ArticleId
                var noArticleId = TempArticleSalesRepository.Select(sale => sale.ArticleId == null || sale.ArticleId.Value == 0);

                foreach (var sale in noArticleId)
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.RegistroSinArticleId, sale.Id),
                        ErrorType = ErrorType.Error,
                        Id = count,
                        Workaround = Workaround.MissingArticleId,
                        Param1 = sale.ArticleName,
                        Param2 = sale.ArticleCode,
                        Param3 = sale.Date.ToString("yyyyMM"),
                        Param4 = sale.StoreName,
                        ItemId = sale.Id
                    });
                }

                //Registros sin StoreId
                var noStoreId = TempArticleSalesRepository.Select(sale => sale.StoreId == null || sale.StoreId.Value == 0);

                foreach (var sale in noStoreId)
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.RegistroSinStoreId, sale.Id),
                        ErrorType = ErrorType.Error,
                        Id = count,
                        Workaround = Workaround.MissingStoreId,
                        Param1 = sale.ArticleName,
                        Param2 = sale.Date.ToString("yyyyMM"),
                        Param3 = sale.StoreCode,
                        Param4 = sale.StoreName,
                        ItemId = sale.Id
                    });
                }

                //Ventas repeditas para misma Articulo - Tienda - Fecha (Solo para ventas diarias)
                if (Country.DailySales)
                {
                    var repeatedSales = TempArticleSalesRepository.SelectDuplicates();

                    var q = from rep in repeatedSales
                            group rep by new { rep.StoreName, rep.ArticleName, rep.Date } into g
                            where g.Count() > 1
                            select new { g.Key.ArticleName, g.Key.StoreName, g.Key.Date, Count = g.Count() };


                    foreach (var rep in q)
                    {
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.Validation,
                            Description = string.Format(Messages.VentasDuplicadas, rep.Count, rep.StoreName, rep.ArticleName, rep.Date.ToString("yyyyMM")),
                            ErrorType = ErrorType.Warning,
                            Id = count,
                            Workaround = Workaround.Duplicated,
                            Param1 = rep.StoreName,
                            Param2 = rep.ArticleName,
                            Param3 = rep.Date.ToString("yyyyMM"),
                            Param4 = rep.Count.ToString()
                        });
                    }
                }

                //Resumen de la Importación
                var importSummary = TempArticleSalesSummaryRepository.Select().First();

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = string.Format(Messages.TotalMesesImportados, importSummary.Dates),
                    ErrorType = ErrorType.Information,
                    Workaround = Workaround.ImportSummary,
                    Id = count
                });
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = string.Format(Messages.TotalTiendasImportadas, importSummary.Stores),
                    ErrorType = ErrorType.Information,
                    Workaround = Workaround.ImportSummary,
                    Id = count
                });
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = string.Format(Messages.TotalArticulosImportados, importSummary.Articles),
                    ErrorType = ErrorType.Information,
                    Workaround = Workaround.ImportSummary,
                    Id = count
                });

                //Promedio de Venta x tienda x mes
                var storesAverage = TempArticleSalesStoresAverageRepository.Select();

                foreach (var storeAvg in storesAverage)
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.PromedioVentasMensualPorTienda, storeAvg.StoreName, storeAvg.Date.ToString("yyyyMM")),
                        ErrorType = ErrorType.Information,
                        Workaround = Workaround.StoresAverage,
                        Id = count,
                        Param1 = storeAvg.StoreName,
                        Param2 = storeAvg.Date.ToString("yyyyMM"),
                        Param3 = storeAvg.Sales.ToString(),
                        Param4 = storeAvg.Margin.ToString()
                    });
                }


            }


            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
                {                   

                    //Hago Update de las ventas del pais (solo las que tienen ventas y unidades mayor a cero)
                    ArticleSalesRepository.UpdateFromTempArticleSales();

                    //Hago Insert de las ventas del pais (solo las que tienen ventas y unidades mayor a cero)
                    ArticleSalesRepository.InsertFromTempArticleSales();

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempArticleSalesRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = "Las ventas se han importado correctamente."
                });
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }



        //Inventarios
        public IList<ImporterResult> ImportInventoryMasterStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetInventoryMasterFilePath(Country, rangeReview.Title);

                if (Directory.Exists(sourceDirectoryPath))
                {
                    string[] files = Directory.GetFiles(sourceDirectoryPath);

                    if (files.Length == 0)
                    {
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.PreRequisites,
                            Description = Errors.NoFilesToImport,
                            ErrorType = ErrorType.Error,
                            Id = errorCount
                        });
                    }
                    else
                    {
                        importerResults.Add(new ImporterResult
                        {
                            ErrorType = ErrorType.Information,
                            Category = ResultCategory.PreRequisites,
                            Description = Messages.ChequeoArchivosCorrecto
                        });
                    }

                    //Inventory
                    foreach (string file in files)
                    {
                        fileName = file;

                        //Chequeo que la fecha este en el archivo en el fomato establecido
                        string date = fileName.Substring(fileName.LastIndexOf('.') - 8, 8);

                        DateTime parsedDate;

                        if (!DateTime.TryParse(date, out parsedDate))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                Description = string.Format(Errors.InvalidFileNameFormat, fileName),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        string[] fields;
                        rowNumber = 1;
                        using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                        {
                            line = sr.ReadLine();

                            if (!line.Replace(" ", string.Empty).Contains("Store|SKU|Description|Quantity|Cost|Retail|GP%"))
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterInvalidHeaderFormat,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
                                });
                            }

                            line = sr.ReadLine();
                            rowNumber++;
                            fields = line.Split('|');
                            if (fields.Count() != 7)
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterErrorInvalidNumberOfFields,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCantidadDeCampos, fileName)
                                });
                            }

                        }
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
                }
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportInventoryMasterStep2()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempInventories].
                TempInventoriesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetInventoryMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);


                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempInventoryRow>();
                List<TempInventoryRow> tempInventories = new List<TempInventoryRow>();
                TempInventoryRow row;
                decimal quantity;
                decimal cost;
                decimal retail;
                decimal gp;


                #region Importo los inventarios
                foreach (string file in files)
                {
                    tempInventories = new List<TempInventoryRow>();

                    fileName = file;
                    rowNumber = 0;

                    string date = fileName.Substring(fileName.LastIndexOf('.') - 8, 8);

                    DateTime parsedDate;
                    DateTime.TryParse(date, out parsedDate);

                    string[] fields;



                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store|SKU|Description|") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = line.Split('|');

                                if (fields != null && fields.Count() == 7)
                                {

                                    decimal.TryParse(fields[3], out quantity);
                                    decimal.TryParse(fields[4], out cost);
                                    decimal.TryParse(fields[5], out retail);
                                    decimal.TryParse(fields[6].Trim().Substring(0, fields[6].Trim().LastIndexOf('%')), out gp);

                                    row = new TempInventoryRow();
                                    row.Date = parsedDate;
                                    row.StoreCode = fields[0];
                                    row.ArticleCode = fields[1].Trim().PadLeft(13, '0');
                                    row.Description = fields[2];
                                    row.Quantity = quantity;
                                    row.Cost = cost;
                                    row.Retail = retail;
                                    row.GP = gp;

                                    tempInventories.Add(row);
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }


                            }
                        }
                    }


                    #region Sumarizo y Genero DataTable

                    var q = from inv in tempInventories
                            group inv by new { inv.Date, inv.StoreCode, inv.ArticleCode, inv.Description } into g
                            select new
                            {
                                Date = g.Key.Date,
                                StoreCode = g.Key.StoreCode,
                                ArticleCode = g.Key.ArticleCode,
                                Description = g.Key.Description,
                                Quantity = g.Average(x => x.Quantity),
                                Cost = g.Average(x => x.Cost),
                                Retail = g.Average(x => x.Retail),
                                GP = g.Average(x => x.GP)
                            };

                    foreach (var inv in q)
                    {
                        DataRow datarow = sourceTable.NewRow();

                        datarow["Date"] = inv.Date;
                        datarow["StoreCode"] = inv.StoreCode;
                        datarow["ArticleCode"] = inv.ArticleCode;
                        datarow["Description"] = inv.Description;
                        datarow["Quantity"] = inv.Quantity;
                        datarow["Cost"] = inv.Cost;
                        datarow["Retail"] = inv.Retail;
                        datarow["GP"] = inv.GP;

                        sourceTable.Rows.Add(datarow);
                    }
                    #endregion

                    // new method: SQLBulkCopy:
                    TempInventoriesRepository.BulkCopy(sourceTable);

                }
                #endregion

                

                //Actualizo los Ids (Stores, Articles)
                TempInventoriesRepository.UpdateIds(rangeReview.Id);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportInventoryMasterStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                //Verifico si hay inventarios sin tienda en el Maestro.
                List<TempInventoryRow> storeNotFound = TempInventoriesRepository.Select(x => x.StoreId == 0);

                foreach (TempInventoryRow inv in storeNotFound)
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.InventoryStoreNotFound, inv.StoreCode),
                        ErrorType = ErrorType.Warning,
                        Workaround = Workaround.MissingStoreId,
                        Id = count
                    });
                }

                if (!storeNotFound.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.NoSeEncontraronTiendasInexistentesEnMaestro,
                    });
                }

                //Verifico si hay inventarios sin articulos en el Maestro.
                List<ArticleRow> articleNotFound = TempInventoriesRepository.SelectNewArticles(rangeReview.Id);

                foreach (ArticleRow art in articleNotFound)
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.InventoryArticleNotFound, art.Code),
                        ErrorType = ErrorType.Warning,
                        Workaround = Workaround.MissingArticleId,
                        Param1 = art.Code,
                        Param2 = art.Name,
                        Param3 = art.Brand,
                        Param4 = art.Manufacturer,
                        Param5 = art.CategoryCode,
                        Id = count
                    });
                }

                if (!articleNotFound.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = Messages.NoSeEncontraronArticulosInexistentesEnMaestro
                    });
                }


                //Costos nulos
                List<TempInventoryRow> nullCosts = TempInventoriesRepository.Select(x => x.Cost == null);

                foreach (TempInventoryRow inv in nullCosts)
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format("El articulo {0} en la tienda {1} para la fecha {2} tiene costo nulo", inv.Description, inv.StoreCode, inv.Date.ToString("MMM-yyyy")),
                        ErrorType = ErrorType.Warning,
                        Workaround = Workaround.NullCosts,
                        Id = count
                    });
                }

                if (!storeNotFound.Any())
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.Validation,
                        Description = "No se encontraron Inventarios con costos nulos."
                    });
                }

            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportInventoryMasterStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 15, 0));

                using (scope)
                {
                    //Hago Insert de los Articulos Nuevos
                    ArticlesRepository.InsertFromTempInventories(rangeReview.Id);

                    //Hago Update del Maestro de Inventarios
                    InventoriesRepository.UpdateFromTempInventories();

                    //Hago Insert del Maestro Inventarios
                    InventoriesRepository.InsertFromTempInventories(rangeReview.Id);

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempInventoriesRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetInventoryMasterFilePath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = Messages.LosInventariosSeImportaronCorrectamente
                });

            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        public void WriteTextResults(string fileName, IList<ImporterResult> results)
        {
            string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
            string fullName = Path.Combine(resultsPath, fileName + ".txt");

            using (StreamWriter sw = new StreamWriter(fullName, false))
            {
                if (results.Count > 0)
                {
                    foreach (ImporterResult res in results)
                    {
                        sw.WriteLine(res.CreateErrorMessageSingleLineFormated);
                    }
                }
                else
                {
                    sw.WriteLine("Success going through this step!");
                }
            }
        }

    }
}
